# -*- coding:utf-8 -*-
import numpy as np
import pandas as pd
from sklearn import datasets, linear_model
from sklearn.metrics import mean_squared_error, r2_score
from sklearn import linear_model       #导入线性模型
import MySQLdb.cursors
import DateUtil
from sklearn import tree
import datetime
from sklearn.cross_validation import train_test_split
# import HolidayDate
data = pd.read_csv('dataHaveCommType.csv',names=['commodity_type','commodity_id','insert_time','product_id','price'])
products=data['product_id'].values
insert_times=data['insert_time'].values
commodity_type=data['commodity_type'].values
commodity_id=data['commodity_id'].values
price=data['price'].values

# regr = linear_model.LinearRegression()
treeRegr = tree.DecisionTreeRegressor()
#按照商品id进行分组，对每个商品分别建模
modelNum=0
badNum=0
# holidays=HolidayDate.getHoliday()

#连接数据库
conn = MySQLdb.connect(host='192.168.0.64', user='root', passwd='111111', db='spider_online', port=3306,
                           charset='utf8')
cur = conn.cursor()
delete_sql='delete from commodity_price_predict'
cur.execute(delete_sql)
conn.commit()
#将数据转换为列表类型并进行划分
today = datetime.date.today()
tenDayAgo=int((today + datetime.timedelta(days=-10)).strftime("%Y%m%d"))
for name,group in data.groupby(['commodity_type','product_id','commodity_id']):
    if np.max(group['insert_time'])>tenDayAgo:
        x_data = []
        y_data = group['price'].values
        for i in range(len(group['commodity_id'].values)):
            xx1=group['commodity_id'].values[i]
            xx2=group['insert_time'].values[i]
            xx4=group['product_id'].values[i]
            x_data.append([xx2])
        x_train, x_test, y_train, y_test = train_test_split(x_data, y_data, test_size=0.3, random_state=0)
        #进行回归拟合
        if len(x_test)>0 and len(x_train)>0:
            modelNum+=1
            treeRegr.fit(x_train, y_train)
            y_pred = treeRegr.predict(x_test)
            y_train_pred=treeRegr.predict(x_train)
            oneWeekFuture=DateUtil.getFutureOneWeek(str(np.max(group['insert_time'])))
            futureData=treeRegr.predict(oneWeekFuture)
            score=r2_score(y_test, y_pred)
            for i in range(len(futureData)):
                sql="insert into commodity_price_predict (product_id,commodity_id,pre_price,pre_date,business_code,model_score,company_code) values(%s,%s,%s,%s,'%s',%s,'%s')" \
                    %(name[1],name[2],futureData[i],oneWeekFuture[i][0],"TICKET",round(score,2),"LMM_C_CODE")
                # print sql
                cur.execute(sql)
                conn.commit()
            if r2_score(y_test, y_pred)<0.6:
                badNum += 1
print badNum
print modelNum
cur.close()
conn.close()